package com.site.dao.impl;

import com.site.dao.GoodsTypeDao;
import com.site.pojo.GoodsType;
import com.site.utils.JDBCUtils;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

/**
 * FileName: GoodsTypeDaoImpl.java
 * 货物种类表DaoImpl
 *
 * @author 田尚忠
 * @Date    2021/11/28
 * @version 1.00
 */
public class GoodsTypeDaoImpl implements GoodsTypeDao {

    @Override
    public List<GoodsType> selectAll() throws SQLException {
        List<GoodsType> list = new ArrayList<>();
        Connection conn = JDBCUtils.getConnection();
        String sql = "SELECT `tid`, `size_id`, `tname`, `quantity` FROM `goods_type`";
        ResultSet rs = JDBCUtils.executeQuery(conn, sql);
        while (rs.next()){
            GoodsType goodsType = new GoodsType();
            goodsType.setTid(rs.getLong("tid"));
            goodsType.setSizeId(rs.getLong("size_id"));
            goodsType.setTname(rs.getString("tname"));
            goodsType.setQuantity(rs.getLong("quantity"));
            list.add(goodsType);
        }
        JDBCUtils.close(rs,null,conn);
        return list;
    }

    @Override
    public List<GoodsType> selectByTname(String name) throws SQLException {
        List<GoodsType> list = new ArrayList<>();
        Connection conn = JDBCUtils.getConnection();
        String sql = "SELECT `tid`, `size_id`, `tname`, `quantity` FROM `goods_type` WHERE tname LIKE '%"+name+"%'";
        ResultSet rs = JDBCUtils.executeQuery(conn, sql);
        while (rs.next()){
            GoodsType goodsType = new GoodsType();
            goodsType.setTid(rs.getLong("tid"));
            goodsType.setSizeId(rs.getLong("size_id"));
            goodsType.setTname(rs.getString("tname"));
            goodsType.setQuantity(rs.getLong("quantity"));
            list.add(goodsType);
        }
        JDBCUtils.close(rs,null,conn);
        return list;
    }


    @Override
    public GoodsType selectByTid(int id) throws SQLException {
        GoodsType goodsType = new GoodsType();
        Connection conn=JDBCUtils.getConnection();
        String sql ="SELECT `tid`, `size_id`, `tname`, `quantity` FROM `goods_type` WHERE tid=?";
        ResultSet rs = JDBCUtils.executeQuery(conn, sql, id);
        while (rs.next()){
            goodsType.setTid(rs.getLong("tid"));
            goodsType.setSizeId(rs.getLong("size_id"));
            goodsType.setTname(rs.getString("tname"));
            goodsType.setQuantity(rs.getLong("quantity"));
        }
        JDBCUtils.close(rs,null,conn);
        return goodsType;
    }

    @Override
    public int addGoodsType(GoodsType goodsType) throws SQLException {
        String sql = "INSERT INTO `hurricane_house`.`goods_type` (`tid`, `tname`, `quantity`) VALUES (?,?,0);";
        return JDBCUtils.executeUpdate(sql,new Object[]{goodsType.getTid(),goodsType.getTname()});
    }

    @Override
    public int addGoodsType(Connection conn, GoodsType goodsType) {
        String sql = "INSERT INTO `hurricane_house`.`goods_type` (`tid`, `tname`, `quantity`) VALUES (?,?,0);";
        return JDBCUtils.executeUpdate(sql,new Object[]{conn,goodsType.getTid(),goodsType.getTname()});
    }

    @Override
    public int delGoodsTypeByid(int id) {
        String sql = "DELETE FROM goods_type WHERE size_id = ?";
        return JDBCUtils.executeUpdate(sql,new Object[]{id});
    }

    @Override
    public int delGoodsTypeByid(Connection conn, int id) {
        String sql = "DELETE FROM goods_type WHERE size_id = ?";
        return JDBCUtils.executeUpdate(conn,sql,new Object[]{id});
    }

    @Override
    public int delGoodsTypeByTid(int id) {
        String sql = "DELETE FROM goods_type WHERE tid = ?";
        return JDBCUtils.executeUpdate(sql,new Object[]{id});
    }

    @Override
    public int delGoodsTypeByTid(Connection conn, int id) {
        String sql = "DELETE FROM goods_type WHERE tid = ?";
        return JDBCUtils.executeUpdate(conn,sql,new Object[]{id});
    }

    //没有事务按照id删除
    @Override
    public int updateGoodsTypeByid(int id,GoodsType goodsType) {
        String sql = "UPDATE `hurricane_house`.`goods_type` SET `tname` = ?, `quantity` = ? WHERE `size_id` = ?;";
        return JDBCUtils.executeUpdate(sql,new Object[]{goodsType.getTname(),goodsType.getQuantity(),id});
    }
    //有事务按照id删除
    @Override
    public int updateGoodsTypeByid(Connection conn, int id,GoodsType goodsType) {
        String sql = "UPDATE `hurricane_house`.`goods_type` SET `tname` = ?, `quantity` = ? WHERE `size_id` = ?;";
        return JDBCUtils.executeUpdate(conn,sql,new Object[]{goodsType.getTname(),goodsType.getQuantity(),id});
    }
    //没有事务按照种类id删除
    @Override
    public int updateGoodsTypeByTid(int id, GoodsType goodsType) {
        String sql = "UPDATE `hurricane_house`.`goods_type` SET  `tname` = ?, `quantity` = ? WHERE `tid` = ?;";
        return JDBCUtils.executeUpdate(sql,new Object[]{goodsType.getTname(),goodsType.getQuantity(),id});
    }

    //有事务按照种类id删除
    @Override
    public int updateGoodsTypeByTid(Connection conn, int id, GoodsType goodsType) {
        String sql = "UPDATE `hurricane_house`.`goods_type` SET  `tname` = ?, `quantity` = ? WHERE `tid` = ?;";
        return JDBCUtils.executeUpdate(conn,sql,new Object[]{goodsType.getTname(),goodsType.getQuantity(),id});
    }
}
